
from flask import Flask, render_template, request, redirect
import pymysql

conn = pymysql.connect(
  host='127.0.0.1',
  user='root',
  password='',
  port=3306,
  db='todo',
  charset='utf8'
)

app = Flask("待办项网站")

def query_data():
  """从数据库查询数据"""
  sql = """
    select * from todo_list
    order by deadline asc
  """
  cursor = conn.cursor(pymysql.cursors.DictCursor)
  cursor.execute(sql)
  return cursor.fetchall()

def query_single_data(id):
  """从数据库查询单条数据"""
  sql = f"""
      select * from todo_list
      where id = {id}
    """
  cursor = conn.cursor(pymysql.cursors.DictCursor)
  cursor.execute(sql)
  return cursor.fetchone()

def insert_to_db(content, deadline):
  """新增数据到mysql"""
  sql = f"""
    insert into todo_list
    (content, deadline)
    values ('{content}', '{deadline}')
  """
  cursor = conn.cursor()
  cursor.execute(sql)
  conn.commit()

def update_db(id, content, deadline):
  """更新数据"""
  sql = f"""
      update todo_list
      set content='{content}', deadline='{deadline}'
      where id={id}
    """
  cursor = conn.cursor()
  cursor.execute(sql)
  conn.commit()

def delete_db(id):
  """删除数据库的id的记录"""
  sql = f"""
        delete from todo_list
        where id={id}
      """
  cursor = conn.cursor()
  cursor.execute(sql)
  conn.commit()

@app.route("/delete", methods=["GET"])
def delete():
  id = request.args.get("id")
  delete_db(id)
  return redirect("/")

@app.route("/edit", methods=["GET", "POST"])
def edit():
  id = request.args.get("id")
  data = query_single_data(id)

  if request.method == "POST":
    content = request.form.get("content")
    deadline = request.form.get("deadline")
    update_db(id, content, deadline)
    return redirect("/")

  return render_template("edit.html", data=data)

@app.route("/", methods=["GET", "POST"])
def index():

  if request.method == "POST":
    content = request.form.get("content")
    deadline = request.form.get("deadline")
    insert_to_db(content, deadline)

  datas = query_data()

  # alt enter，快速引入
  return render_template("index.html", datas=datas)

app.run(debug=True)